/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.edu.pucp.dp1.persistencia.bd;

import java.rmi.*;
import java.rmi.server.*;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author javier
 */
public class BaseDatos extends UnicastRemoteObject implements IBaseDatos {

    Properties misPropiedades;
    public int ANCHO;
    public int LARGO;
    int tamanioFila;
    int tamanioColumna;
    int distanciaEntreAvenidas;
    int diasXSemana;
    int cantHorasAlDia;
    int velocidad_default;
    String velocidad_horizontal_multiplos;
    String velocidad_vertical_multiplos;
    //JAVIER : atributos derivados
    int numColumnas;
    int numFilas;
    int numeroZonas;
    int[] cantAutosxHora;
    int[] zonas;
    int[][] patrones;
    String[][] tabla;
    String[][] historico; //es la representacion en String de patrones para copiar a BD
    int numColumnasEnMapa;
    //JAVIER: otros;
    String pathScriptTablaPatron;
    //String pathVehiculos;
    //String pathIntersecciones;
    //String pathDatosIntervalos;
    String pathScriptTablaHistorico;
    String pathScriptLimitesVelocidad;
    //Javier: conexion a base de datos;
    String JDBC_DRIVER;
    String DB_URL;
    String USER;
    String PASS;
    //JAvier: otros mas
    ArrayList multiplicidadHorizontal = new ArrayList();
    ArrayList multiplicidadVertical = new ArrayList();
    String mensajeError = "";

    public BaseDatos() throws RemoteException {
        //Constructor obligatorio;
        CargarConfiguracionDeVariablesIniciales();
    }

    //SEGURIDAD=========================================================================
    public boolean AutorizarAcceso(String usuario, String contrasenia) throws RemoteException {
        Connection conn;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            //String query = "select columnname from tablename ;";
            String query = "select * from usuario";
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                String bdUsuario = rs.getString(2);
                String bdContrasenia = rs.getString(3);

                if (bdUsuario.equals(usuario) && bdContrasenia.equals(contrasenia)) {
                    return true;
                };
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

        }

        return false;
    }

    //PROCESAMIENTO=========================================================================
    public ArrayList GetZonasAAplicarAlgoritmo(int dia, int hora) throws RemoteException {
        Connection conn;
        ArrayList rpta = new ArrayList();
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            String sdia = DiaCorrespondiente(dia);
            //ResultSet rs = stmt.executeQuery("SELECT zona, "+sdia+" FROM patron LIKE '%1%';");
            ResultSet rs = stmt.executeQuery("SELECT zona, " + sdia + " FROM patron ;");

            while (rs.next()) {
                String cadena = rs.getString(2);
                char charAt = cadena.charAt(hora);
                if (charAt == '1') {
                    String zona = rs.getString(1);
                    int z = Integer.parseInt(zona);
                    rpta.add(z);
                }
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        }

        return rpta;
    }
    public int GetVelocidadAvenida(String sentido, int numeroAvenida) throws RemoteException {
        int rpta = 40;
        Connection conn;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            //ResultSet rs = stmt.executeQuery("SELECT zona, "+sdia+" FROM patron LIKE '%1%';");
            ResultSet rs = stmt.executeQuery("SELECT *  FROM limites WHERE avenida=" + numeroAvenida + " AND sentido ='" + sentido + " ';");
            while (rs.next()) {
                String cadena = rs.getString(3);
                rpta = Integer.parseInt(cadena);
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rpta;

    }
    public Punto CoordenadasDeZona(int zona) throws RemoteException {
        //JAVIER: las zonas se nominan horizontalmente, empezando por la zona 0 (Cero)
        Punto rpta = new Punto(0, 0);
        int fila = zona / numColumnas;
        double columna = (double) zona % (double) numColumnas;
        int x = (fila + 1) * 1000;
        int y = (int) columna * 1000;
        rpta.setX(x);
        rpta.setY(y);   
        return rpta;
    }
    public int ZonaDeCoordenadas(int x, int y) throws RemoteException {
        double fila = 0;
        double columna = 0;
        //fila = Math.ceil((double) y / (double) tamanioFila) - 1;
        //columna = Math.ceil((double) x / (double) tamanioColumna) - 1;
        //fila = (int) (Math.floor((double)y/(double)tamanioFila) -1) >0 ?(int) (Math.floor((double)y/(double)tamanioFila) -1):0;
        //columna = (int) (Math.floor((double)x/(double)tamanioColumna) -1) >0 ? (int) (Math.floor((double)x/(double)tamanioColumna) -1):0;
        fila = (int) (y/tamanioFila);
        columna = (int) (x/tamanioColumna);
        double zona = numColumnasEnMapa * fila + columna;
        //System.out.println("Zona: "+zona);
        return (int) zona ;
    }
    public void SetZonaAAplicarAlgoritmo(int zona, int dia, int hora, boolean aplicarAlgoritmo) throws RemoteException {
        Connection conn;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            String sdia = DiaCorrespondiente(dia);
            //ResultSet rs = stmt.executeQuery("SELECT zona, "+sdia+" FROM patron LIKE '%1%';");
            ResultSet rs = stmt.executeQuery("SELECT zona, " + sdia + " FROM patron WHERE zona=" + zona + ";");

            while (rs.next()) {
                String cadena = rs.getString(2);
                String nuevaCadena = "";
                char charAt = cadena.charAt(hora);
                for (int i = 0; i < cadena.length(); i++) {
                    if (i == hora) {
                        if (aplicarAlgoritmo) {
                            nuevaCadena = nuevaCadena + "1";
                        } else {
                            nuevaCadena = nuevaCadena + "0";
                        }
                    } else {
                        nuevaCadena = nuevaCadena + cadena.charAt(i);
                    }
                }
                Statement st = (Statement) conn.createStatement();
                st.executeUpdate("update patron set " + sdia + "='" + nuevaCadena + "' where zona=40;");
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    public boolean AgregarInterseccion(int x, int y) throws RemoteException {
        Connection conn;
        boolean auxReturn=false;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            //String query = "select columnname from tablename ;";
            int zona = ZonaDeCoordenadas(x, y);
            String query = "select * from interseccion where zona = " + zona + " AND "
                    + "x = " + x + " AND y = " + y + ";";
            ResultSet rs = stmt.executeQuery(query);
            int cont = 0;
            while (rs.next()) {
                cont++;
            }
            if (cont == 0) {
                if(stmt.executeUpdate("INSERT INTO interseccion VALUES(" + zona + " , " + x + "," + y + ");")>0){
                    auxReturn=true;
                }
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

        }
        return auxReturn;
    }
    public boolean EliminarInterseccion(int x, int y) throws RemoteException {
        Connection conn;
        boolean auxReturn=false;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            //String query = "select columnname from tablename ;";
            int zona = ZonaDeCoordenadas(x, y);
            String cadena = "DELETE FROM interseccion WHERE zona=" + zona + " AND x=" + x + " AND y=" + y + " ;";
            //System.out.println(cadena);
            if(stmt.executeUpdate(cadena)>0){
                auxReturn=true;
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

        }
        return auxReturn;
    }
    
    //METODOS DE APOYO=====================================================================
    public static String DiaCorrespondiente(int i) throws RemoteException {
        String rpta = "";
        switch (i) {
            case 1:
                rpta = "lunes";
                break;
            case 2:
                rpta = "martes";
                break;
            case 3:
                rpta = "miercoles";
                break;
            case 4:
                rpta = "jueves";
                break;
            case 5:
                rpta = "viernes";
                break;
            case 6:
                rpta = "sabado";
                break;
            case 7:
                rpta = "domingo";
                break;
        }
        return rpta;
    }
    private void CargarConfiguracionDeVariablesIniciales() {
        this.misPropiedades = new MisPropiedades().getProperties();
        this.ANCHO = Integer.parseInt(this.misPropiedades.getProperty("ANCHO"));
        this.LARGO = Integer.parseInt(this.misPropiedades.getProperty("LARGO"));
        this.tamanioFila = Integer.parseInt(this.misPropiedades.getProperty("tamanioFila"));
        this.tamanioColumna = Integer.parseInt(this.misPropiedades.getProperty("tamanioColumna"));
        this.velocidad_default = Integer.parseInt(this.misPropiedades.getProperty("velocidad_default"));
        this.velocidad_horizontal_multiplos = this.misPropiedades.getProperty("velocidad_horizontal_multiplos");
        this.velocidad_vertical_multiplos = this.misPropiedades.getProperty("velocidad_vertical_multiplos");
        this.distanciaEntreAvenidas = Integer.parseInt(this.misPropiedades.getProperty("distanciaEntreAvenidas"));
        this.diasXSemana = Integer.parseInt(this.misPropiedades.getProperty("diasXSemana"));
        this.cantHorasAlDia = Integer.parseInt(this.misPropiedades.getProperty("cantHorasAlDia"));

        //JAVIER: campos derivados de los atributos anteriores;
        numColumnas = ANCHO / tamanioColumna; //240
        numFilas = LARGO / tamanioFila; //160
        numeroZonas = numColumnas * numFilas; //240 * 160;
        cantAutosxHora = new int[cantHorasAlDia];
        zonas = new int[numeroZonas];
        patrones = new int[numeroZonas][diasXSemana * cantHorasAlDia];
        tabla = new String[numeroZonas][diasXSemana];
        historico = new String[numeroZonas][diasXSemana];
        numColumnasEnMapa = ANCHO / tamanioColumna;

        //JAVIER: otros campos
        pathScriptTablaPatron = this.misPropiedades.getProperty("pathScriptTablaPatron");
        pathScriptTablaHistorico = this.misPropiedades.getProperty("pathScriptTablaHistorico");
        pathScriptLimitesVelocidad = this.misPropiedades.getProperty("pathScriptLimitesVelocidad");

        //JAVIER : conexion a base de datos;
        JDBC_DRIVER = this.misPropiedades.getProperty("JDBC_DRIVER");
        DB_URL = this.misPropiedades.getProperty("DB_URL");
        USER = this.misPropiedades.getProperty("USER");
        PASS = this.misPropiedades.getProperty("PASS");
    }

    public ArrayList<Punto> InterseccionesDeZona(int x, int y) throws RemoteException {
        ArrayList<Punto> rpta = new ArrayList<Punto>();
        Connection conn;
        int zona = ZonaDeCoordenadas(x, y);
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            //String query = "select columnname from tablename ;";
            String query = "select * from interseccion where zona ="+zona;
            ResultSet rs = stmt.executeQuery(query);
            Punto p;
            while (rs.next()) {
                String bdx = rs.getString(2);
                String bdy = rs.getString(3);
                int intx= Integer.parseInt(bdx);
                int inty= Integer.parseInt(bdy);
                p= new Punto(intx, inty);
                rpta.add(p);
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

        }

        return rpta;
    }
    
    public ArrayList<Punto> InterseccionesDeMapa()throws RemoteException{
        ArrayList<Punto> rpta = new ArrayList<Punto>();
        Connection conn;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            //String query = "select columnname from tablename ;";
            String query = "select * from interseccion";
            ResultSet rs = stmt.executeQuery(query);
            Punto p;
            while (rs.next()) {
                String bdx = rs.getString(2);
                String bdy = rs.getString(3);
                int intx= Integer.parseInt(bdx);
                int inty= Integer.parseInt(bdy);
                p= new Punto(intx, inty);
                rpta.add(p);
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(BaseDatos.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

        }
        return rpta;
    }
}
